﻿using Microsoft.Data.Sqlite;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.DirectoryServices.ActiveDirectory;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace FileImportSystem2.Services
{
    internal class dbExportServer : IDisposable
    {
        private readonly string _dbFd = AppConfig.dbMonFd;
        private readonly string _dbPath;
        private readonly SqliteConnection _db = null!;
        private readonly IProgress<string>? _progress;
        public bool _isInit = true;
        public string _initError = string.Empty;
        public string _dbMonth = string.Empty;

        #region 实现 IDisposable 接口
        //IDisposable是 .NET 中的一个重要接口，它提供了一种​​确定性的资源释放机制​​，主要用于管理和释放非托管资源
        //.NET 的垃圾回收器（GC）能自动管理托管内存的释放，但它无法自动释放非托管资源。非托管资源是指不受 CLR（公共语言运行时）直接管理的资源
        //如果这些资源不及时释放，会导致内存泄漏和资源耗尽。IDisposable接口通过 Dispose()方法让开发者能够​​显式地、及时地​​释放这些资源
        private bool _disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    // 释放托管资源
                    _db?.Close();
                    _db?.Dispose();
                }
                _disposed = true;
            }
        }
        #endregion

        public dbExportServer(string dateMonth, IProgress<string>? progress = null)
        {
            _dbMonth = dateMonth;
            _dbPath = Path.Combine(_dbFd, _dbMonth + ".db");
            _progress = progress;
            // 检查路径
            if (!File.Exists(_dbPath))
            {
                _isInit = false;
                _initError = "数据库文件不存在";
                _progress?.Report($"导出数据异常:{_initError}");
                return;
            }
            // 初始化数据库连接
            _db = new SqliteConnection($"Data Source={_dbPath}");
            _db.Open();
        }

        public bool TryExportData(string sql, out string filePath, string outputDir = "", string fileName = "")
        {
            // 检查初始化状态
            if (_isInit == false)
            {
                _progress?.Report($"导出数据异常:{_initError}");
                filePath = string.Empty;
                return false;
            }
            // 确认输出路径
            if (outputDir == "")
            {
                outputDir = AppConfig.GetSafeAppDataPath();
            }
            else if (!Directory.Exists(outputDir))
            {
                Directory.CreateDirectory(outputDir);
            }
            // 确认输出文件名
            if (fileName == "")
            {
                fileName = $"export_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
            }
            string fullPath = Path.Combine(outputDir, fileName);
            // 删除已存在的文件
            if (File.Exists(fullPath))
            {
                try
                {
                    _progress?.Report($"删除已存在的文件:{fullPath}");
                    File.Delete(fullPath);
                }
                catch (Exception ex)
                {
                    _progress?.Report($"删除失败:{ex.Message}");
                    filePath = string.Empty;
                    return false;
                }
            }
            // 执行查询
            try
            {
                using var cmd = _db.CreateCommand();
                cmd.CommandText = sql + " limit 1000000";

                using (var reader = cmd.ExecuteReader())
                {
                    MiniExcel.SaveAs(fullPath, reader, true);
                }

                filePath = fullPath;
                return true;
            }
            catch (SqliteException ex)
            {
                _progress?.Report($"SQL执行失败: {ex.Message}");
                filePath = string.Empty;
                return false;
            }
            catch (Exception ex)
            {
                _progress?.Report($"导出过程异常: {ex.Message}");
                filePath = string.Empty;
                return false;
            }
        }

        /// <summary>
        /// 根据SQL语句获取数据量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Int64 GetDataCount(string sql)
        {
            // 检查初始化状态
            if (_isInit == false)
            {
                _progress?.Report($"获取数据量异常:{_initError}");
                return -1;
            }
            try
            {
                using var cmd = _db.CreateCommand();
                cmd.CommandText = sql;
                long recordCount = Convert.ToInt64(cmd.ExecuteScalar());
                return recordCount;
            }
            catch (SqliteException ex)
            {
                _progress?.Report($"SQL执行失败: {ex.Message}");
                return -1;
            }
            catch (Exception ex)
            {
                _progress?.Report($"获取数据量过程异常: {ex.Message}");
                return -1;
            }
        }

        /// <summary>
        /// 导出未匹配数据
        /// </summary>
        /// <param name="outputDir"></param>
        /// <returns></returns>
        public bool TryExportUnMatchedDataOld(out string outputDir)
        {

            // 检查初始化状态
            if (_isInit == false)
            {
                _progress?.Report($"数据库初始化异常:{_initError}");
                outputDir = string.Empty;
                return false;
            }

            // 确认输出路径
            string dtStr = DateTime.Now.ToString("yyyyMMdd_HHmmss");
            outputDir = Path.Combine(AppConfig.GetSafeAppDataPath(), $"exp_UnMatched_{dtStr}");

            // 执行查询-交错数组
            long totalCount = 0;
            string[][] exportTasks = new string[][]
            {
                   new string[] {" date,单号,扫描人,发件仓", " from senderdata where isMatched = 0", "发件数据" },
                   new string[] {" date,单号,结算对象,揽收业务员,分部,Is分部Matched,发件仓,Is发件仓Matched", " from ShenzhouData where Is发件仓Matched = 0 or Is分部Matched = 0", "神州数据" },
                   new string[] {" Date,单号,结算对象,分部", " from OutboundInvoiceData where IsMatched = 0", "出港账单" },
                   new string[] {" Date,单号,结算对象,分部", " from CenterTransferFeeData where IsMatched = 0", "中转费" }


            };
            foreach (string[] task in exportTasks)
            {
                string field = task[0];
                string sqlRight = task[1];
                string sql = $"select {field} {sqlRight}";
                string fileNamePrefix = task[2];

                long recordCount = GetDataCount($"select count(*) {sqlRight}");
                totalCount += recordCount;
                if (recordCount > 0)
                {
                    if (!Directory.Exists(outputDir))
                    {
                        Directory.CreateDirectory(outputDir);
                    }
                    try
                    {
                        //  使用 out _ 忽略不需要的 filePath 返回值
                        //TryExportData(sql ,out _, outputDir, $"{fileNamePrefix}_{dtStr}");
                        TryExportData(sql, out string expF, outputDir, $"{fileNamePrefix}_UnMatched_{dtStr}.xlsx");
                        _progress?.Report($"导出文件: {expF} , 记录数: {recordCount}");
                    }
                    catch (Exception ex)
                    {
                        _progress?.Report($"导出失败 {fileNamePrefix}: {ex.Message}");
                    }
                }
            }

            return (totalCount > 0);
        }


        public bool TryExportUnMatchedData(out string outputDir)
        {
            string dtStr = DateTime.Now.ToString("yyyyMMdd_HHmmss");
            outputDir = Path.Combine(AppConfig.GetSafeAppDataPath(),"exp", $"{_dbMonth}_exp_UnMatched_{dtStr}");

            var exportTasks = new string[][]
            {
                new string[] {"date,单号,扫描人,发件仓", "from senderdata where isMatched = 0", $"{_dbMonth}_发件数据_UnMatched"},
                new string[] {"date,单号,结算对象,揽收业务员,分部,Is分部Matched,发件仓,Is发件仓Matched",
                             "from ShenzhouData where Is发件仓Matched = 0 or Is分部Matched = 0",$"{_dbMonth}_神州数据_UnMatched"},
                new string[] {"Date,单号,结算对象,分部", "from OutboundInvoiceData where IsMatched = 0", $"{_dbMonth}_出港账单_UnMatched"},
                new string[] {"Date,单号,结算对象,分部", "from CenterTransferFeeData where IsMatched = 0", $"{_dbMonth}_中转费_UnMatched"},
                new string[] {"*", "from UnMatchedData", $"{_dbMonth}_补充库"}

            };

            return ExecuteExportTasks(exportTasks, outputDir, dtStr);
        }
        public bool TryExportResultData(out string outputDir)
        {

            string dtStr = DateTime.Now.ToString("yyyyMMdd_HHmmss");
            outputDir = Path.Combine(AppConfig.GetSafeAppDataPath(),"exp", $"exp_统计_{dtStr}");



            var exportTasks = new string[][]
            {
                   new string[] {
                       " 分部,结算对象,电子面单账号名称,count(单号) as 单号数量,sum(结算重量_kg) as 总结算重量,sum(派费合计) as 总派费合计,sum(中转费合计) as 总中转费合计",
                       " from OutboundInvoiceData group  by 分部,结算对象,电子面单账号名称",
                       "出港账单统计"
                   },
                   new string[] {
                       " 分部,结算对象,客户名称,count(单号) as 单号数量,sum(中转费合计) as 总中转费,sum(结算重量) as 总结算重量",
                       " from CenterTransferFeeData group by 分部,结算对象,客户名称"
                       , "中心中转费统计"
                   }
            };

            if (TryGetTableHz())
            {
                var taskList = exportTasks.ToList();
                taskList.Add(new string[] {
                    " *",
                    " from hz ",
                    "汇总表"
                });
                exportTasks = taskList.ToArray();
            }

            return ExecuteExportTasks(exportTasks, outputDir, dtStr);
        }

        private bool ExecuteExportTasks(string[][] tasks, string outputDir, string dtStr)
        {
            if (!_isInit)
            {
                _progress?.Report($"数据库初始化异常:{_initError}");
                return false;
            }

            long totalCount = 0;
            bool hasDataToExport = false;

            // 预检查是否有数据需要导出
            foreach (var task in tasks)
            {
                if (GetDataCount($"select count(*) {task[1]}") > 0)
                {
                    hasDataToExport = true;
                    break;
                }
            }

            if (!hasDataToExport)
            {
                _progress?.Report("没有需要导出的数据");
                return false;
            }

            if (!Directory.Exists(outputDir))
            {
                Directory.CreateDirectory(outputDir);
            }

            foreach (string[] task in tasks)
            {
                string field = task[0].Trim();
                string sqlRight = task[1];
                string sql = $"select {field} {sqlRight}";
                string fileNamePrefix = task[2];

                long recordCount = GetDataCount($"select count(*) {sqlRight}");
                if (recordCount > 0)
                {
                    totalCount += recordCount;
                    try
                    {
                        string fileName = $"{fileNamePrefix}_{recordCount}_{dtStr}.xlsx";
                        if (TryExportData(sql, out string expF, outputDir, fileName))
                        {
                            _progress?.Report($"✓ 导出成功: {Path.GetFileName(expF)}, 记录数: {recordCount}");
                        }
                        else
                        {
                            _progress?.Report($"✗ 导出失败: {fileNamePrefix}");
                        }
                    }
                    catch (Exception ex)
                    {
                        _progress?.Report($"✗ 导出异常 {fileNamePrefix}: {ex.Message}");
                    }
                }
            }

            _progress?.Report(totalCount > 0 ?
                $"导出完成，总记录数: {totalCount}" :
                "没有数据被导出");

            return totalCount > 0;
        }


        /// <summary>
        /// 生成汇总表
        /// </summary>
        /// <returns></returns>
        private bool TryGetTableHz()
        {
            try
            {
                RunSql(_db, "drop table if exists Hz");
                RunSql(_db, "create table if not exists Hz( " +
                    "date string UNIQUE, " +
                    "发件量 float," +
                    "有偿派费 float," +
                    " 中心中转费 float, " +
                    "旺季中转费 float)");

                // 神州明细按日期统计数量
                RunSqlInTransaction(_db,
                    "insert into Hz (date, 发件量) " +
                    "select date,(count(单号)) as 发件量 " +
                    "from shenzhoudata " +
                    "group by date");

                // 出港账单按日期求和派费合计
                RunSqlInTransaction(_db,
                    "insert into hz(date) select distinct date from OutboundInvoiceData where date not in (select date from Hz)"
                    );

                RunSqlInTransaction(_db,
                    "update Hz set 有偿派费 = ( " +
                    "select sum(派费合计) from OutboundInvoiceData o " +
                    "where o.date = Hz.date )");

                // 中心中转费按日期求和中转费合计
                RunSqlInTransaction(_db,
                    "insert into hz(date) select distinct date from CenterTransferFeeData where date not in (select date from Hz)"
                    );
                RunSqlInTransaction(_db,
                    "update Hz set 中心中转费 = ( " +
                    "select sum(中转费合计) from CenterTransferFeeData c " +
                    "where c.date = Hz.date )");

                // 中心中转费 按日期统计中转费>0的数量 * 0.1
                RunSqlInTransaction(_db,
                    "update Hz set 旺季中转费 = ( " +
                    "select count(单号)*0.1 from CenterTransferFeeData c " +
                    "where c.date = Hz.date and c.中转费合计 > 0 )");

                return true;
            }
            catch (Exception ex)
            {
                _progress?.Report($"更新汇总数据异常: {ex.Message}");
                return false;
            }

        }

        private void RunSql(SqliteConnection db, string sql)
        {

            var cmd = new SqliteCommand(sql, db);
            cmd.ExecuteNonQuery();
        }

        private void RunSqlInTransaction(SqliteConnection db, string sql)
        {
            using SqliteTransaction transaction = db.BeginTransaction();
            {
                using SqliteCommand cmd = new SqliteCommand(sql, db);
                {
                    cmd.Transaction = transaction;
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }
    }
}
